package com.nccsjz.back.product.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.nccsjz.pojo.News;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.base.BaseDAO;
import com.nccsjz.utils.Pager;

/**
 * 新闻的CRUD操作
 * */
public class NewsDAO extends BaseDAO {
	
    private boolean flag;
	public NewsDAO(Connection con) {
		super(con);
	}
	//添加新闻
	public boolean addNews(News news) throws SQLException{
		try{
    		String sql="insert into news(newstitle,newscontent,newsdate) values(?,?,?)";
    		pstmt=con.prepareStatement(sql);
    		pstmt.setString(1,news.getNewstitle());
    		pstmt.setString(2, news.getNewscontent());
    		if(null!=news.getNewsdate()){
    		 pstmt.setDate(3, DateUtil.convertDate(news.getNewsdate()));
    		}else{
    			pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
    		}
    		int num=pstmt.executeUpdate();
    		if(num!=0){
    			flag=true;
    		}
    	}catch(Exception ex){
    		ex.printStackTrace();
    	}finally{
    		closeAll(rs,pstmt);
    	}
    	return flag;
	}
    //修改新闻
	public boolean updateNews(News news) throws SQLException{
    	try{
    		String sql="update news set newstitle=?,newscontent=? where newsid=?";
    	    pstmt=con.prepareStatement(sql);
    	    pstmt.setString(1, news.getNewstitle());
    	    pstmt.setString(2, news.getNewscontent());
    	   // pstmt.setDate(3, DateUtil.convertDate(news.getNewsdate()));
    	    pstmt.setLong(3, news.getNewsid());
    	    int num=pstmt.executeUpdate();
    	    if(num!=0){
    	    	flag=true;
    	    }
    	}catch(Exception ex){
    		ex.printStackTrace();
    	}finally{
    	closeAll(rs,pstmt);
    	}
    	return flag;
    }
	//根据主键获取一条新闻
	public News getNews(Long id) throws SQLException{
    	News news=null;
    	try{
    		
    		String sql="select * from news where newsid=?";
    		pstmt=con.prepareStatement(sql);
    		pstmt.setLong(1, id);
    		rs=pstmt.executeQuery();
    	    if(rs.next()){
    	    	  news=new News();
    	    	  news.setNewsid(rs.getLong("newsid"));
    	    	  news.setNewstitle(rs.getString("newstitle"));
    	    	  news.setNewscontent(rs.getString("newscontent"));
    	    	  news.setNewsdate(rs.getDate("newsdate"));
    	    }
    	}catch(Exception ex){
    		ex.printStackTrace();
    	}finally{
    	closeAll(rs, pstmt);
    	}
    	return news;
    }
	//根据主键删除新闻
	 public boolean deleteNews(long id) throws SQLException{
	    	try{
	    		//conn=super.getConn();
	    		String sql="delete from news where newsid=?";
	    		pstmt=con.prepareStatement(sql);
	    		pstmt.setLong(1, id);
	    		int num=pstmt.executeUpdate();
	    		if(num!=0){
	    			flag=true;
	    		}
	    	}catch(Exception ex){
	    		ex.printStackTrace();
	    	}finally{
	    		closeAll(rs,pstmt);
	    	}
	    	return flag;
	    }
	 //分页处理
	 public Pager getNews(int pageSize,int pageNo) throws SQLException{
    	 List<News> list=new ArrayList<News>();
    	 int rownum=0;
    	 try{
    	 String sql1="select count(*) from news";
    	 String sql2="select * from(select u.*,rownum rn from(select * from news order by newsdate desc) u where rownum<=?)where rn>?";
    	 
    	 pstmt=con.prepareStatement(sql1);
    	 rs=pstmt.executeQuery();
    	 if(rs.next()){
    		 rownum=rs.getInt(1);
    	 }
    	 pstmt=con.prepareStatement(sql2);
    	 pstmt.setInt(1, pageNo*pageSize);
    	 pstmt.setInt(2, (pageNo-1)*pageSize);
    	 rs=pstmt.executeQuery();
    	 while(rs.next()){
    	   News news=new News();
    	   news.setNewsid(rs.getLong("newsid"));
      	   news.setNewstitle(rs.getString("newstitle"));
      	   news.setNewscontent(rs.getString("newscontent"));
      	   news.setNewsdate(rs.getDate("newsdate"));
      	   list.add(news);
    	 }
    	 }catch(Exception ex){
    		 ex.printStackTrace();
    	 }finally{
    		 closeAll(rs,pstmt);
    	 }
    	 Pager pager=new Pager(pageSize,pageNo,rownum,list);
    	 return pager;
     }
	 //批量删除
	 public boolean deleteBathNews(List<Long> list)  throws SQLException{
		 int num[]=null;
		 try{
			String sql="delete from news where newsid=?";
			pstmt=con.prepareStatement(sql);
			for(int i=0;i<list.size();i++){
				 pstmt.setLong(1,list.get(i));
				 pstmt.addBatch();
			}
			num=pstmt.executeBatch();
			if(num!=null){
				flag=true;
			}
			 
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 closeAll(rs,pstmt);
			 
		 }
		 return flag;
	 }
	 /**
	  * 得到前n条新闻。
	  * @return
	 * @throws SQLException 
	  */
	 public List<News> getTopN(int n) throws SQLException{
		 List<News> list=new ArrayList<News>();
    	 int rownum=0;
    	 try{
    		 if(n<=0){
    			 throw new Exception("取得的记录条数不能为0或负数");
    		 }
    	 String sql2="select * from(select u.*,rownum rn from(select * from news order by newsid desc) u )where rn<=?";
    	 
    	
    	 pstmt=con.prepareStatement(sql2);
    	 pstmt.setInt(1, n);
    	 
    	 rs=pstmt.executeQuery();
    	 while(rs.next()&&rownum<=n){
    	   News news=new News();
    	   news.setNewsid(rs.getLong("newsid"));
      	   news.setNewstitle(rs.getString("newstitle"));
      	   news.setNewscontent(rs.getString("newscontent"));
      	   news.setNewsdate(rs.getDate("newsdate"));
      	   list.add(news);
      	   rownum++;
    	 }
    	 }catch(Exception ex){
    		 ex.printStackTrace();
    	 }finally{
    		 closeAll(rs,pstmt);
    	 }
		 return list;
	 }
}

